﻿using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace ASP.NET5.Helper
{
    public enum DBType
    {
        SqlServer = 0,
        Oracle = 1,
        MySql = 2,
        OleDb = 3
    }
    public class DbConnectionHelper : IDisposable
    {        
        public static DbConnection DbConnection { get; set; }
        public static DbCommand DbCommand { get; set; }
        public static DbDataAdapter DbDataAdapter { get; set; }
        public static DbConnection GetConnection(DBType dbType, string connStr)
        {
            switch (dbType)
            {
                case DBType.SqlServer:
                    DbConnection = new SqlConnection(connStr);
                    break;
                case DBType.MySql:
                    DbConnection = new MySqlConnection(connStr);
                    break;
                case DBType.Oracle:
                    DbConnection = new OracleConnection(connStr);
                    break;
            }
            return DbConnection;
        }

        public static DbCommand GetCommand(DBType dbType)
        {
            switch (dbType)
            {
                case DBType.SqlServer:
                    DbCommand = new SqlCommand();
                    break;
                case DBType.MySql:
                    DbCommand = new MySqlCommand();
                    break;
                case DBType.Oracle:
                    DbCommand = new OracleCommand();
                    break;
            }
            return DbCommand;
        }

        public static DbDataAdapter GetDataAdapter(DBType dbType)
        {
            switch (dbType)
            {
                case DBType.SqlServer:
                    DbDataAdapter = new SqlDataAdapter();
                    break;
                case DBType.MySql:
                    DbDataAdapter = new MySqlDataAdapter();
                    break;
                case DBType.Oracle:
                    DbDataAdapter = new OracleDataAdapter();
                    break;
            }
            return DbDataAdapter;
        }

        /// <summary>
        /// Must Close Connection after use
        /// </summary>
        public void Dispose()
        {
            if (DbConnectionHelper.DbConnection != null)
            {
                DbConnectionHelper.DbConnection.Dispose();
            }
            if (DbConnectionHelper.DbCommand != null)
            {
                DbConnectionHelper.DbCommand.Dispose();
            }
            if (DbConnectionHelper.DbDataAdapter != null)
            {
                DbConnectionHelper.DbDataAdapter.Dispose();
            }
        }

        public static int ExecuteNonQuery(DBType dBType, string commandText, CommandType commandType = CommandType.Text)
        {
            using (DbConnection conn = DbConnectionHelper.GetConnection(dBType, commandText))
            {
                using (DbCommand cmd = DbConnectionHelper.GetCommand(dBType))
                {
                    PreparCommand(conn, cmd, commandText, commandType);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 不带参数的设置sqlcommand对象
        /// </summary>
        /// <param name="conn">sqlconnection对象</param>
        /// <param name="cmd">sqlcommmand对象</param>
        /// <param name="commandText">sql语句或存储过程名称</param>
        /// <param name="commandType">语句的类型</param>
        private static void PreparCommand(DbConnection conn, DbCommand cmd, string commandText, CommandType commandType)
         {
             //打开连接
             if (conn.State != ConnectionState.Open)
             {
                conn.Open();
             }
            //设置SqlCommand对象的属性值
             cmd.Connection = conn;
             cmd.CommandType = commandType;
             cmd.CommandText = commandText;
             cmd.CommandTimeout = 60;
         }
    }
}
